package org.warehouse.dao.engine;

import java.io.IOException;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.PostConstruct;
import javax.annotation.PreDestroy;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.SqlRowSetResultSetExtractor;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Service;
import org.warehouse.dao.exception.DaoException;

@Service
public class DbExecutor implements IDbExecutor {

    public static final Logger logger = LoggerFactory.getLogger(DbExecutor.class);

    @Value("${jdbc.url}")
    private String url;

    @Value("${jdbc.username}")
    public String username;

    @Value("${jdbc.password}")
    public String password;

    private Connection connection;

    public DbExecutor() throws ClassNotFoundException {
        Class.forName("org.h2.Driver");
    }

    @PostConstruct
    public void DbExecutorConfigurator() throws SQLException, IOException {
        connection = DriverManager.getConnection(getUrl(), username, "");
    }

    @PreDestroy
    public void closeConnection() {
        logger.debug("Close connection and driver");
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            try {
                Driver driver = DriverManager.getDriver(getUrl());
                DriverManager.deregisterDriver(driver);
            } catch (SQLException e) {
                logger.error(e.getMessage(), e);
            }
        }
    }

    private String getUrl() {
        return url + ";runscript from 'classpath:sql.sql';TRACE_LEVEL_SYSTEM_OUT=2";
    }

    private Connection getConnection() {
        return connection;
    }

    private void closePreparedStatement(PreparedStatement pstmt) {
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                logger.error(e.getMessage(), e);
            }
        }
    }

    @Override
    public SqlRowSet invoke(String sql) throws DaoException {
        return invoke(sql, null);
    }

    @Override
    public int invokeUpdate(String sql) throws DaoException {
        return invokeUpdate(sql, null);
    }

    @Override
    public synchronized SqlRowSet invoke(String sql, List<DataObject<?>> dataList) throws DaoException {
        logger.debug(sql, dataList);

        if (!sql.startsWith("SELECT")) {
            throw new DaoException("Unsupported a request " + sql + ".Try to use invokeUpdate method");
        }

        PreparedStatement pstmt = null;

        try {
            pstmt = getConnection().prepareStatement(sql);

            prepareStatement(pstmt, dataList);

            ResultSet resultSet = pstmt.executeQuery();
            SqlRowSetResultSetExtractor resultSetExtractor = new SqlRowSetResultSetExtractor();
            SqlRowSet sqlRowSet = resultSetExtractor.extractData(resultSet);

            closePreparedStatement(pstmt);

            return sqlRowSet;
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
            closePreparedStatement(pstmt);
            throw new DaoException(e);
        }
    }

    @Override
    public synchronized int invokeUpdate(String sql, List<DataObject<?>> dataList) throws DaoException {
//        logger.debug(sql, dataList);
        
        if(!(sql.startsWith("INSERT") || sql.startsWith("UPDATE") || sql.startsWith("DELETE"))) {
            throw new DaoException("Unsupported a request " + sql + ". The request must be started with [SELECT | UPDATE | DELETE | INSERT] statements");
        }
        
        PreparedStatement pstmt = null;

        try {
            pstmt = getConnection().prepareStatement(sql);

            prepareStatement(pstmt, dataList);
            
            int result = pstmt.executeUpdate();

            closePreparedStatement(pstmt);
            
            return result;
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
            closePreparedStatement(pstmt);
            throw new DaoException(e);
        }
    }

    private void prepareStatement(PreparedStatement pstmt, List<DataObject<?>> dataList) throws SQLException {
        if (dataList == null || dataList.isEmpty()) {
            return;
        }

        int iterator = 0;
        for (DataObject<?> dataObject : dataList) {
            iterator++;
            if (dataObject.getType() == Integer.class) {
                Integer intValue = dataObject.getData();
                pstmt.setInt(iterator, intValue.intValue());
            } else if (dataObject.getType() == String.class) {
                String strValue = dataObject.getData();
                pstmt.setString(iterator, strValue);
            } else if (dataObject.getData() == null) {
                pstmt.setObject(iterator, null);
            } else {
                throw new DaoException(dataObject.getType() + " hasn't been supported yet");
            }
        }
    }

}
